﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DAO;
using DataAccess;
using System.Data;
namespace BUL
{
    public class ExperienceBUL
    {
        ConnectDatabase cd = new ConnectDatabase();
        public DataTable load(String id)
        {
            String sql = "select experience.position,experience.startDate,experience.finishDate,experience.Organization from experience,staff where experience.idExperience=staff.idExperience and staff.idStaff='" + id + "'";
            return cd.ExcuteQuery(sql);
        }
        public Boolean insert(ExperienceDAO p)
        {
            String sql = @"insert into experience (position, startDate, finishDate, organization) values(@position,@startDate,@finishDate,@organization)";
            int count = 4;
            String[] name = new String[count];
            object[] value = new object[count];
            name[0] = "@position";
            name[1] = "@startDate";
            name[2] = "@finishDate";
            name[3] = "@organization";
            value[0] = p.Position;
            value[1] = p.StartDate;
            value[2] = p.FinishDate;
            value[3] = p.Organization;
            return cd.ExcuteNonQuery(sql, name, value, count);
        }
        public Boolean update(ExperienceDAO p)
        {
            String sql = @"update experience set position=@position, startDate=@startDate, finishDate=@finishDate, organization=@organization where idExperience=@id";
            int count = 5;
            String[] name = new String[count];
            object[] value = new object[count];
            name[0] = "@position";
            name[1] = "@startDate";
            name[2] = "@finishDate";
            name[3] = "@organization";
            name[4] = "@id";
            value[0] = p.Position;
            value[1] = p.StartDate;
            value[2] = p.FinishDate;
            value[3] = p.Organization;
            value[4] = p.IdExperience;
            return cd.ExcuteNonQuery(sql, name, value, count);
        }
        public Boolean delete(String id)
        {
            String sql = @"delete from experience where idExperience = '" + id + "'";
            return cd.ExcuteNonQuery(sql);
        }
        public int getIdLast()
        {
            String sql = @"select MAX(idExperience) from experience";
            DataTable dt = cd.ExcuteQuery(sql);
            return Int32.Parse(dt.Rows[0][0].ToString());
        }
        public int getIdByStaff(String id)
        {
            String sql = @"select idExperience from staff where idStaff='"+id+"'";
            DataTable dt = cd.ExcuteQuery(sql);
            return Int32.Parse(dt.Rows[0][0].ToString());
        }
    }
}
